more excel headaches :(

"mr_gofast" (jake_berenshteyn) 07/14/2014 at 15:16 • Filed to: None

1

32

whats the format to create a formula to ignore certain cells?

i have records with either CN railway company, CP railway company or other, how do i count up any record thats arent CN or CP?, since there are so many company that aren't CP or CN there is no column specifically for them,

here have a mazda

bob and john > mr_gofast 07/14/2014 at 15:19

2

I know how to count selected cells (either by row or by column) but I'm not sure if its what you are looking for...

so, say i want to add the values in them together. I go =SUM(B4:B6 + B8:B12) and just add on or take away as you like)

mr_gofast > bob and john 07/14/2014 at 15:20

0

im trying to create a formula where it will scan a given column and ignore certain records (cn and cp) and add up the occurences of the rest.

duurtlang > mr_gofast 07/14/2014 at 15:24

1

There are many ways I guess, and I doubt my way is the most effective. I'd add another column.

Column A: the column you've already got with the railways.

Column B: (example for cell B2) =if(A2=CN;"";(if(A2=CP;"";A2))) Obviously you'd need to replace CN or CP with the appropriate name. If you insert a name, do it as "name" or as a link to a cell with the right name. With this formula it'll create a "" (or blank) whenever there's a CN or CP and gives the value of column A if there's something else.

Like I said, probably not the most efficient way. Also, I wrote this from memory, so check it.

cazzyodo > mr_gofast 07/14/2014 at 15:26

1

I'm in Excel all day and have taught myself as much as possible. Feel free to ask me any time, I love problem solving.

Try =countifs(RANGE,"<>cn",RANGE,"<>cp")

Should count anything that isn't cn or cp. I did a quick check and it worked.

Diesel > mr_gofast 07/14/2014 at 15:27

1

You want to use the =COUNTIF(A1:A10,"cn") or You can put write it like this "*cn*" if there is any other text around it. Also, this has to be entered as an array formula. CTRL, SHIFT, Enter to add this.

mr_gofast > duurtlang 07/14/2014 at 15:27

0

is there a symbol for does not equal? ideally i want a formula like this;

if column B:B(railways column) doesnt equal CN or CP then classify it as OTH and keep a total in another box, so if ther are 7 records that arent CN or CP i want a 7 in hte output cell

Jayhawk Jake > mr_gofast 07/14/2014 at 15:29

1

I'm not sure how to do it the best way, but there's a way to do it

=COUNTA( [range] )-(COUNTIF( [range] ,"CN")-COUNTIF( [range] ,"CP")

That counts all cells that aren't blank, then subtracts the count of cells that contain CN or CP.

You could probably use COUNTIFS instead of COUNTIF an combine the two COUNTIF. I think that let's you do and/or.

There's also probably a much better way to do this...

mr_gofast > cazzyodo 07/14/2014 at 15:30

0

youre awesome!

Jayhawk Jake > mr_gofast 07/14/2014 at 15:30

0

Do you just mean to count them, or do you want to add a value that corresponds to them?

mr_gofast > cazzyodo 07/14/2014 at 15:34

0

here is the formula so far and its giving me a huge nubmer when its supposed to be 1.

=COUNTIFS('2014-2015 BIP'!E:E,"<>CN",'2014-2015 BIP'!E:E,"<>CP")

thats supposed to be if column E doesnt equal CN or CP then add up how many times that occurs

mr_gofast > Jayhawk Jake 07/14/2014 at 15:36

0

i need to count how many times there are records that are not CN or CP in a given column

Jayhawk Jake > mr_gofast 07/14/2014 at 15:50

1

Yeah, my other reply should do that. If you want to select the whole column your range would be X:X where X is the column

cazzyodo > mr_gofast 07/14/2014 at 15:54

1

I'll give it a whip again...procrastination at work is always best spent with a purpose.

cazzyodo > mr_gofast 07/14/2014 at 15:57

1

I just did the same thing with the column...it counts every cell in the column, including blanks. If you go to the very last row of Excel it will be that huge number plus how many cells are CN and CP. Clearly define the range (like E2:E3456 or whatever) and it should work. I'll see if I can figure out one for entire columns so it's easier in the future.

cazzyodo > mr_gofast 07/14/2014 at 15:58

1

Got it. Your formula is now =COUNTIFS('2014-2015 BIP'!E:E,"<>CN",'2014-2015 BIP'!E:E,"<>CP",'2014-2015 BIP'!E:E,"<>")

mr_gofast > cazzyodo 07/14/2014 at 17:39

2

awesome thanks brother.

Jayhawk Jake > mr_gofast 07/14/2014 at 20:55

1

That's because the column goes to infinity. Are there blanks? If not, add another condition "<>"""

theloudmouth > mr_gofast 07/15/2014 at 06:31

1

StackOverflow.com is the gospel

mr_gofast > cazzyodo 07/15/2014 at 11:57

0

hey buddy,,,next evolution of the project is to add in another condition - dates.i need to tabulate that formula and to be able too see data month to month..so all records dated in april, all records in may .etc..

=COUNTIFS('2014-2015 BIP'!E:E,"CP",'2014-2015 BIP'!B:B, "ONT",'2014-2015 BIP'!D:D,"<04/31/2014")

is what ive got so far...trying to get it to count any record dated less then month end.

cazzyodo > mr_gofast 07/15/2014 at 12:03

0

So you want to see the total count since the end of April? Will you be doing this for every month going forward or for a changing date range?

You may want to try something with the crossproduct function. I use that a lot because you can actually use it for counts, averages, sums, etc with a date range. I put two cells in the first row or on a summary tab, use them to set the date range and then my formula cranks away and gives me once cell. I have a series of them for this report I run every quarter and all I have to do now is change those two numbers and everything calculates out. I can even do a report for the year! Took a while to design but literally takes 10 seconds to do reports now.

mr_gofast > cazzyodo 07/15/2014 at 12:16

0

well now what my boss wants is to be able to say - pull up april and see how many records match april, he wants to have seperate tables for each month..auto updating as i add new records...so in the month of april there was 15 events based on the data, 5 in february etc..

cazzyodo > mr_gofast 07/15/2014 at 12:27

0

Let me see if I can come up with something you can try. Then you can see if you like it and incorporate it into something.

I'm assuming you have the date each one is done in listed in column A, right?

cazzyodo > mr_gofast 07/15/2014 at 12:29

0

Actually, that's easy enough to do with countifs...the other way is way more complicated so let's run with that first haha

mr_gofast > cazzyodo 07/15/2014 at 12:34

0

my date column is D - format is short date or long date..i can pick either. So i need to indicate how many records occurred in each month

cazzyodo > mr_gofast 07/15/2014 at 12:45

0

Would this work or do you want to specifically call out each month like I mentioned underneath the summary box (formula at the top)?

mr_gofast > cazzyodo 07/15/2014 at 12:49

0

more like calling out each month like the summary you chose.

cazzyodo > mr_gofast 07/15/2014 at 12:51

0

So the one I boxed off would work? Then you just specify date range and the company abbreviation.

mr_gofast > cazzyodo 07/15/2014 at 12:53

0

the one with all the months listed and the # signs

cazzyodo > mr_gofast 07/15/2014 at 12:54

0

Ah...give me a sec.

cazzyodo > mr_gofast 07/15/2014 at 13:09

0

This is rough haha

mr_gofast > cazzyodo 07/15/2014 at 13:15

0

youre telling me...im trying to figure out what im doing wrong...all it is, is one extra condition in the formula. i was checking by railway cn/cp by region and now by date..what could be so hard.

cazzyodo > mr_gofast 07/15/2014 at 13:19

1

I resorted to the sumproduct method I was telling you about. Can't just click the column but if you just do the second row to some ridiculous row number it works.

Granted, this method changes all the counts based on what you put in F2.